IS NOT NULL Condition

This SQL tutorial explains how to use the SQL IS NOT NULL condition with syntax and examples.

Description

The SQL IS NOT NULL Condition is used to test for a NOT NULL value in SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the SQL IS NOT NULL Condition is:

expression IS NOT NULL

Parameters or Arguments

expression

The expression to test for a NOT NULL value.

Note

• If expression is not a NULL value, the condition evaluates to TRUE.
• If expression is a NULL value, the condition evaluates to FALSE.

Example - With SELECT Statement

Let's look at an example of how to use SQL IS NOT NULL in a SQL SELECT statement:

SELECT *

FROM customers

WHERE customer_name IS NOT NULL;

This SQL IS NOT NULL example will return all records from the customers table where the customer_name does not contain a NULL value.

Example - With INSERT Statement

Next, let's look at an example of how to use SQL IS NOT NULL in a SQL INSERT statement:

INSERT INTO suppliers

(supplier_id, supplier_name)

SELECT account_no, name

FROM customers

WHERE account_no IS NOT NULL;

This SQL IS NOT NULL example will insert records into the suppliers table where the account_no does not contain a NULL value in the customers table.

Example - With UPDATE Statement

Next, let's look at an example of how to use SQL IS NOT NULL in a SQL UPDATE statement:

UPDATE suppliers

SET supplier_name = 'Apple'

WHERE supplier_name IS NOT NULL;

This SQL IS NOT NULL example will update records in the suppliers table where the supplier_name does not contain a NULL value.

Example - With DELETE Statement

Next, let's look at an example of how to use SQL IS NOT NULL in a SQL DELETE statement:

DELETE FROM customers

WHERE status IS NOT NULL;

This SQL IS NOT NULL example will delete all records from the customers table where the status does not contain a NULL value.